﻿Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports System.Text
Public Class uLMSNewMachineStepPlanBusiness
    Implements IBusiness.uLMSNewMachineStepPlanIBusiness

    ''' <summary>
    ''' 查询新机订单，产品，工艺数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetNewMachineStepData(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSNewMachineStepPlanIBusiness.GetNewMachineStepData
        Dim strSql As String = <string>
SELECT ut.attributevalue EngineNo,m.mfgordername,pb2.productname,p.productid,wb.workflowname,w.workflowid,m.plannedcompletiondate,m.plannedstartdate FROM mfgorder m
LEFT JOIN Userattribute ut ON ut.parentid=m.mfgorderid AND ut.userattributename='发动机'
LEFT JOIN productbase pb ON pb.productbaseid=m.productbaseid
LEFT JOIN product p ON p.productid=NVL(pb.revofrcdid,m.productid)
LEFT JOIN productbase pb2 ON pb2.productbaseid=p.productbaseid
LEFT JOIN workflow w ON w.productid=p.productid
LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid                
WHERE w.workflowtype='装试新机'</string>

        If para.ContainsKey("FactoryID") Then
            strSql += String.Format(" and m.FactoryID like '%{0}%'", para("FactoryID"))
        End If

        If para.ContainsKey("EngineNo") Then
            strSql += String.Format(" and ut.attributevalue like '%{0}%'", para("EngineNo"))
        End If

        If para.ContainsKey("MfgorderName") Then
            strSql += String.Format(" and m.MfgorderName like '%{0}%'", para("MfgorderName"))
        End If

        If para.ContainsKey("ProductName") Then
            strSql += String.Format(" and pb2.ProductName like '%{0}%'", para("ProductName"))
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql

        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取工步信息，计划信息
    ''' </summary>
    ''' <param name="workflowstepID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetNewMachineStepPlan(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSNewMachineStepPlanIBusiness.GetNewMachineStepPlan
        Dim strSql As String = <string>SELECT distinct ws.workflowstepid,ws.workflowstepname,st.stepno,stb.stepinfoname,st.stepinfoid,st.description stepinfoDes,
nvl(zs.plannedstartdate,zs2.plannedstartdate) plannedstartdate,nvl(zs.plannedfinishdate,zs2.plannedfinishdate) plannedfinishdate,nvl(zs.factfinisheddate,zs2.factfinisheddate)  factfinisheddate,
nvl(zs.zsstepDispatchPlanid,zs2.zsstepDispatchPlanid) zsstepDispatchPlanid,nvl(zs.ExceedNotes,zs2.ExceedNotes) ExceedNotes FROM workflowstep ws 
        LEFT JOIN stepinfo st ON ws.workflowstepid=st.workflowstepid
        LEFT JOIN stepinfobase stb ON stb.stepinfobaseid=st.stepinfoid
        </string>.Value

        strSql += String.Format(" LEFT JOIN zsstepDispatchPlan zs ON zs.workflowid=ws.workflowid AND st.stepinfoid =zs.stepinfoid and zs.mfgordername='{0}' ", para("MfgorderName"))

        strSql += String.Format("  LEFT JOIN zsstepDispatchPlan zs2 ON zs2.workflowid=ws.workflowid AND ws.workflowstepid =zs2.workflowstepid AND zs2.stepinfoid IS NULL and zs2.mfgordername='{0}'  ", para("MfgorderName"))

        strSql += " where 1=1 "

        If para.ContainsKey("WorkflowID") Then
            strSql += String.Format(" and ws.workflowid='{0}'", para("WorkflowID"))
        End If
        If para.ContainsKey("WorkflowStepID") Then
            strSql += String.Format(" and ws.WorkflowStepID='{0}'", para("WorkflowStepID"))
        End If

        strSql += " order by ws.workflowstepid,st.stepno "

        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 保存新机工步计划
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function SaveMachineStepPlan(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSNewMachineStepPlanIBusiness.SaveMachineStepPlan
        SaveMachineStepPlan = False

        Dim insertSql As String = <string>INSERT INTO  zsstepDispatchPlan t
 (t.createdate,t.CreateEmployeeID,t.EngineNo,t.MfgorderName,t.ProductID,WorkflowID,WorkflowStepID,StepinfoID,Stepinfodesc,ZSStepDispatchPlanID,PlannedStartDate,PlannedFinishDate)
 VALUES(SYSDATE,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',to_date('{9}','yyyy/mm/dd'),to_date('{10}','yyyy/mm/dd'))</string>.Value

        Dim updateSql As String = "UPDATE  zsstepDispatchPlan t SET t.plannedstartdate=to_date('{0}','yyyy/mm/dd'),t.plannedfinishdate=to_date('{1}','yyyy/mm/dd') WHERE t.factfinisheddate is null and t.zsstepdispatchplanid='{2}'"
        Dim sql As String = ""
        If String.IsNullOrWhiteSpace(para("ZSStepDispatchPlanID")) Then
            sql = String.Format(insertSql, para("CreateEmployeeID"), para("EngineNo"), para("MfgorderName"), para("ProductID"), para("WorkflowID"),
                                para("WorkflowStepID"), para("StepinfoID"), para("StepinfoDes"), Guid.NewGuid.ToString(), para("PlannedStartDate"), para("PlannedFinishDate"))
        Else
            sql = String.Format(updateSql, para("PlannedStartDate"), para("PlannedFinishDate"), para("ZSStepDispatchPlanID"))
        End If

        If DatabaseHelper.ExecuteSql(sql) Then
            SaveMachineStepPlan = True
        End If

    End Function

    ''' <summary>
    ''' 保存完成信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function SaveMachineStepPlanFinsh(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSNewMachineStepPlanIBusiness.SaveMachineStepPlanFinsh
        SaveMachineStepPlanFinsh = False

        Dim updateSql As String = "UPDATE  zsstepDispatchPlan t SET t.factfinishemployeeid='{0}',t.factfinisheddate=to_date('{1}','yyyy/mm/dd'),t.ExceedNotes='{2}' WHERE t.plannedfinishdate is not null and t.zsstepdispatchplanid='{3}'"

        Dim updateSql2 As String = "UPDATE  zsstepDispatchPlan t SET t.ExceedNotes='{0}' WHERE t.zsstepdispatchplanid='{1}'"


        Dim sql As String = ""
        If para("type") = "0" Then
            sql = String.Format(updateSql, para("FactFinishEmployeeID"), para("FactFinishDate"), para("ExceedNotes"), para("ZSStepDispatchPlanID"))
        ElseIf para("type") = "1" Then
            sql = String.Format(updateSql2, para("ExceedNotes"), para("ZSStepDispatchPlanID"))
        End If


        If DatabaseHelper.ExecuteSql(sql) > 0 Then

            SaveMachineStepPlanFinsh = True

        End If

    End Function


End Class
